﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.Extend;
using Estsh.Core.Model.Extend.Models;
using Estsh.Core.Model.Extend.IFModels;

namespace Estsh.Core.Wms.Repositories
{
    public class InterfaceUserInfoRepository : BaseRepository<SapUser_Extend>, IInterfaceUserInfoRepository
    {
        public InterfaceUserInfoRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        public List<SapUser_Extend> GetUserDateMasterInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT top 5000 * FROM SAP_USER (nolock) ");
                sql.Append("WHERE  SYNFLG = 'N' ");
                sql.Append("ORDER BY RECTIM  ");

                return dbConn.Query<SapUser_Extend>(sql.ToString()).ToList();
            }
        }

        public bool InsertUserDateMasterInfo(List<SapUser_Extend> sapUserList)
        {
            List<DynamicParameters> parametersList;
            DynamicParameters parameters;
            StringBuilder SqlStringBuilder;
            List<string> sqlList;
            SapUser_Extend sapSu= new SapUser_Extend();
            try
            {
                using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
                { 
                    StringBuilder factoryStringBuilder = new StringBuilder(1024);
                    factoryStringBuilder.Append("select * from sys_factory (nolock) where enabled='Y'");//工厂代码获取工厂ID
                    List<SysFactory> sysFactory = dbConn.Query<SysFactory>(factoryStringBuilder.ToString()).ToList();
                    for (int k = 0; k < sysFactory.Count; k++)
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        parameters = new DynamicParameters();

                        for (int i = 0; i < sapUserList.Count; i++)
                        {
                            sqlList = new List<string>();
                            parametersList = new List<DynamicParameters>();
                            sapSu = sapUserList[i];
                            StringBuilder stringBuilder = new StringBuilder();
                            stringBuilder.Append(" SELECT * from sys_emp (nolock) WHERE emp_no='" + sapSu.SerialNo + "' and enabled='Y'");
                            List<SysEmp> sysEmp = dbConn.Query<SysEmp>(stringBuilder.ToString()).ToList();

                            stringBuilder = new StringBuilder();
                            stringBuilder.Append(" SELECT * from sys_dept (nolock) WHERE dept_code='" + sapSu.DepartMent + "' and enabled='Y'");
                            List<SysDept> sysDept = dbConn.Query<SysDept>(stringBuilder.ToString()).ToList();

                            StringBuilder stringShift = new StringBuilder();
                            stringShift.Append(" SELECT * from sys_dept (nolock) WHERE dept_code='" + sapSu.PdLine + "' and enabled='Y'");
                            List<SysDept> sysShift = dbConn.Query<SysDept>(stringShift.ToString()).ToList();
                            int depmentid = sysDept.FirstOrDefault() == null ? 0 : sysDept.FirstOrDefault().DeptId;
                            int shifiid = sysShift.FirstOrDefault() == null ? 0 : sysShift.FirstOrDefault().DeptId;
                            if (sysEmp.Count > 0)
                            {
                                SqlStringBuilder = new StringBuilder(1024);
                                SqlStringBuilder.Append(" UPDATE dbo.sys_emp ");
                                SqlStringBuilder.Append("   SET emp_name = @emp_name ,dept_id = @dept_id,shift_id=@shift_id,company=@company ");
                                SqlStringBuilder.Append("      ,factory_id = @factoryId ");
                                SqlStringBuilder.Append("      ,enabled = @enabled ,update_userid = @updateUserid ");
                                SqlStringBuilder.Append("      ,update_time = @updateTime ");
                                SqlStringBuilder.Append(" WHERE emp_no = @emp_no ");

                                sqlList.Add(SqlStringBuilder.ToString());
                                parameters = new DynamicParameters();
                                parameters.Add("@emp_no", sapSu.SerialNo);//物料编码
                                parameters.Add("@emp_name", sapSu.Name);//物料编码
                                parameters.Add("@dept_id", depmentid);//部门编号
                                parameters.Add("@shift_id", shifiid);//班组编号
                                parameters.Add("@company", sapSu.ItemCode);//工种

                                parameters.Add("@factoryId", sysFactory[k].FactoryId);//工厂ID

                                parameters.Add("@enabled", sapSu.Enabled);//启用
                                parameters.Add("@updateUserid", 0);//更新人
                                parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期

                                parametersList.Add(parameters);
                            }
                            else
                            {
                                SqlStringBuilder = new StringBuilder(1024);
                                SqlStringBuilder.Append("INSERT INTO dbo.sys_emp( emp_no ,emp_name ,dept_id,passwd ,emp_type,shift_id,company ");
                                SqlStringBuilder.Append("           ,factory_id,enabled,create_userid,create_time) ");
                                SqlStringBuilder.Append("     VALUES(@emp_no ,@emp_name,@dept_id,@passwd ,@emp_type,@shift_id,@company  ");
                                SqlStringBuilder.Append("           ,@factoryId,@enabled,@createUserid,@createTime) ");

                                sqlList.Add(SqlStringBuilder.ToString());

                                parameters = new DynamicParameters();
                                parameters.Add("@emp_no", sapSu.SerialNo);//员工号
                                parameters.Add("@emp_name", sapSu.Name);//名称
                                parameters.Add("@passwd", "21232f297a57a5a743894a0e4a801fc3");//初始密码admin
                                parameters.Add("@emp_type", "0");//类型
                                parameters.Add("@dept_id", depmentid);//部门编号
                                parameters.Add("@shift_id", shifiid);//班组编号
                                parameters.Add("@company", sapSu.ItemCode);//工种

                                parameters.Add("@factoryId", sysFactory[k].FactoryId);//工厂ID

                                parameters.Add("@enabled", sapSu.Enabled);//启用
                                parameters.Add("@createUserid", 0);//创建人
                                parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期

                                parametersList.Add(parameters);

                            }

                            SqlStringBuilder = new StringBuilder(1024);
                            SqlStringBuilder.Append("update SAP_USER set SYNFLG='Y',ERRMSG='',SYNTIM=@SYNTIM where GUID=@GUID ");//更新同步状态
                            sqlList.Add(SqlStringBuilder.ToString());

                            parameters = new DynamicParameters();
                            parameters.Add("@GUID", sapSu.Guid);//ID
                            parameters.Add("@SYNTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                            parametersList.Add(parameters);

                            bool staResult = ExecuteTransaction(sqlList, parametersList);
                            if (staResult == false)
                            {
                                sqlList = new List<string>();
                                parametersList = new List<DynamicParameters>();
                                SqlStringBuilder = new StringBuilder(1024);
                                SqlStringBuilder.Append("update SAP_USER set SYNFLG='E',ERRMSG=@ERRMSG,SYNTIM=@SYNTIM  where GUID=@GUID ");//更新同步状态
                                sqlList.Add(SqlStringBuilder.ToString());

                                parameters = new DynamicParameters();
                                parameters.Add("@GUID", sapSu.Guid);//ID
                                parameters.Add("@ERRMSG", "同步错误，请检查接口数据！");//错误
                                parameters.Add("@SYNTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                                parametersList.Add(parameters);
                                ExecuteTransaction(sqlList, parametersList);
                                return false;
                            }
                        }
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                sqlList = new List<string>();
                parametersList = new List<DynamicParameters>();
                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update SAP_USER set SYNFLG='E',ERRMSG=@ERRMSG,SYNTIM=@SYNTIM  where GUID=@GUID ");//更新同步状态
                sqlList.Add(SqlStringBuilder.ToString());

                parameters = new DynamicParameters();
                parameters.Add("@GUID", sapSu.Guid);//ID
                parameters.Add("@ERRMSG", ex.Message);//错误
                parameters.Add("@SYNTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                parametersList.Add(parameters);
                ExecuteTransaction(sqlList, parametersList);
                return false;
            }
        }
    }
}
